<?php 
namespace froyo\cherry\Db;
use froyo\cherry\Base\BaseCompoment;
use froyo\cherry\Base\Cherry;

class Query extends BaseCompoment{
	public $connectionname='';
	protected $select = '*';
    protected $from = null;
    protected $where = null;
    protected $limit = null;
    protected $offset = null;
    protected $join = null;
    protected $orderBy = null;
    protected $groupBy = null;
    protected $having = null;
    protected $grouped = false;
    protected $numRows = 0;
    protected $insertId = null;
    protected $query = null;
    protected $error = null;
    protected $result = [];
    protected $prefix = null;
    protected $op = ['=', '!=', '<', '>', '<=', '>=', '<>'];
    protected $cache = null;
    protected $cacheDir = null;
    protected $queryCount = 0;
    protected $debug = true;
    protected $transactionCount = 0;
    protected $tablealia = null;
    protected $tableAlias = [];
    protected $fetchModel = 'stdClass';
    protected $sqldebug = false;

    public function setFetchModel($fetchModel){
        $this->fetchModel = $fetchModel;
    }
	public function getConnection(){
		return Cherry::$app->getComment('database')->getConnection($this->connectionname);
	}

    public function setPerfix($perfix){
        $this->prefix = $perfix;
    }



    protected function checkperfix($name){
        if(substr($name, 0,2) == '__' && substr($name, -2) == '__')
        {
            $name = $this->perfix.substr($name, 2,strlen($name)-4);
        }
        elseif(strpos($name, $this->perfix)!==0)
        {
            $name = $this->perfix.$name;
        }
        return $name;
    }

    public function table($table)
    {
        if (is_array($table)) {
            $froms = '';
            foreach ($table as $k=>$name) {
                if(is_numeric($k))
                {
                    $froms .= strtolower($name . ', ');
                }
                elseif($k!=$name)
                {
                    $froms .= strtolower($k . ' as '.$name.', ');

                }else
                {
                    $froms .= strtolower($name . ', ');
                }
            }
            $this->from = rtrim($froms, ', ');
        } else {
            $form = explode(',', $table);
            $froms = '';
            foreach ($form as $name) {
                $n = explode(' as ', $name);
                if(count($n) == 2 && $n[0]!=$n[1])
                {
                    $froms .= strtolower($n[0] . ' as '.$n[1].', ');
                }
                elseif(count($n) == 2)
                {
                    $froms .= strtolower($n[0].', ');

                }else
                {
                    $froms .= strtolower($name . ', ');
                }
            }
            $this->from = rtrim($froms, ', ');
        }
        return $this;
    }

    public function select($fields)
    {
        $select = (is_array($fields) ? implode(', ', $fields) : $fields);
        $this->select = ($this->select == '*' ? $select : $this->select . ', ' . $select);
        return $this;
    }
    public function max($field, $name = null)
    {
        $func = 'MAX(' . $field . ')' . (! is_null($name) ? ' AS ' . $name : '');
        $this->select = ($this->select == '*' ? $func : $this->select . ', ' . $func);
        return $this;
    }
    public function min($field, $name = null)
    {
        $func = 'MIN(' . $field . ')' . (! is_null($name) ? ' AS ' . $name : '');
        $this->select = ($this->select == '*' ? $func : $this->select . ', ' . $func);
        return $this;
    }
    public function sum($field, $name = null)
    {
        $func = 'SUM(' . $field . ')' . (! is_null($name) ? ' AS ' . $name : '');
        $this->select = ($this->select == '*' ? $func : $this->select . ', ' . $func);
        return $this;
    }
    public function count($field, $name = null)
    {
        $func = 'COUNT(' . $field . ')' . (! is_null($name) ? ' AS ' . $name : '');
        $this->select = ($this->select == '*' ? $func : $this->select . ', ' . $func);
        return $this;
    }
    public function avg($field, $name = null)
    {
        $func = 'AVG(' . $field . ')' . (! is_null($name) ? ' AS ' . $name : '');
        $this->select = ($this->select == '*' ? $func : $this->select . ', ' . $func);
        return $this;
    }
    public function join($table, $field1 = null, $op = null, $field2 = null, $type = '')
    {
        $on = $field1;
        $table = $this->prefix . $table;
        if (! is_null($op)) {
            $on = (! in_array($op, $this->op) ?
                $this->prefix . $field1 . ' = ' . $this->prefix . $op :
                $this->prefix . $field1 . ' ' . $op . ' ' . $this->prefix . $field2);
        }
        if (is_null($this->join)) {
            $this->join = ' ' . $type . 'JOIN' . ' ' . $table . ' ON ' . $on;
        } else {
            $this->join = $this->join . ' ' . $type . 'JOIN' . ' ' . $table . ' ON ' . $on;
        }
        return $this;
    }
    public function innerJoin($table, $field1, $op = '', $field2 = '')
    {
        $this->join($table, $field1, $op, $field2, 'INNER ');
        return $this;
    }
    public function leftJoin($table, $field1, $op = '', $field2 = '')
    {
        $this->join($table, $field1, $op, $field2, 'LEFT ');
        return $this;
    }
    public function rightJoin($table, $field1, $op = '', $field2 = '')
    {
        $this->join($table, $field1, $op, $field2, 'RIGHT ');
        return $this;
    }
    public function fullOuterJoin($table, $field1, $op = '', $field2 = '')
    {
        $this->join($table, $field1, $op, $field2, 'FULL OUTER ');
        return $this;
    }
    public function leftOuterJoin($table, $field1, $op = '', $field2 = '')
    {
        $this->join($table, $field1, $op, $field2, 'LEFT OUTER ');
        return $this;
    }
    public function rightOuterJoin($table, $field1, $op = '', $field2 = '')
    {
        $this->join($table, $field1, $op, $field2, 'RIGHT OUTER ');
        return $this;
    }
    public function where($where, $op = null, $val = null, $type = '', $andOr = 'AND')
    {

        if (is_array($where)) {
            $_where = [];
            foreach ($where as $column => $data) {
                $_where[] = $type . $column . '=' . $this->escape($data);
            }
            $where = implode(' ' . $andOr . ' ', $_where);
        } else {
            if (is_array($op)) {
                $x = explode('?', $where);
                $w = '';
                foreach ($x as $k => $v) {
                    if (! empty($v)) {
                        $w .= $type . $v . (isset($op[$k]) ? $this->escape($op[$k]) : '');
                    }
                }
                $where = $w;
            } elseif (! in_array($op, $this->op) || $op == false) {
                $where = $type . $where . ' = ' . $this->escape($op);
            } else {
                $where = $type . $where . ' ' . $op . ' ' . $this->escape($val);

            }
        }
        if ($this->grouped) {
            $where = '(' . $where;
            $this->grouped = false;
        }
        if (is_null($this->where)) {
            $this->where = $where;
        } else {
            $this->where = $this->where . ' ' . $andOr . ' ' . $where;
        }

        return $this;
    }
    public function orWhere($where, $op = null, $val = null)
    {
        $this->where($where, $op, $val, '', 'OR');
        return $this;
    }
    public function notWhere($where, $op = null, $val = null)
    {
        $this->where($where, $op, $val, 'NOT ', 'AND');
        return $this;
    }
    public function orNotWhere($where, $op = null, $val = null)
    {
        $this->where($where, $op, $val, 'NOT ', 'OR');
        return $this;
    }
    public function whereNull($where)
    {
        $where = $where . ' IS NULL';
        if (is_null($this->where)) {
            $this->where = $where;
        } else {
            $this->where = $this->where . ' ' . 'AND ' . $where;
        }
        return $this;
    }
    public function whereNotNull($where)
    {
        $where = $where . ' IS NOT NULL';
        if (is_null($this->where)) {
            $this->where = $where;
        } else {
            $this->where = $this->where . ' ' . 'AND ' . $where;
        }
        return $this;
    }
    public function grouped(Closure $obj)
    {
        $this->grouped = true;
        call_user_func_array($obj, [$this]);
        $this->where .= ')';
        return $this;
    }
    public function in($field, array $keys, $type = '', $andOr = 'AND')
    {
        if (is_array($keys)) {
            $_keys = [];
            foreach ($keys as $k => $v) {
                $_keys[] = (is_numeric($v) ? $v : $this->escape($v));
            }
            $keys = implode(', ', $_keys);
            $where = $field . ' ' . $type . 'IN (' . $keys . ')';
            if ($this->grouped) {
                $where = '(' . $where;
                $this->grouped = false;
            }
            if (is_null($this->where)) {
                $this->where = $where;
            } else {
                $this->where = $this->where . ' ' . $andOr . ' ' . $where;
            }
        }
        return $this;
    }
    public function notIn($field, array $keys)
    {
        $this->in($field, $keys, 'NOT ', 'AND');
        return $this;
    }
    public function orIn($field, array $keys)
    {
        $this->in($field, $keys, '', 'OR');
        return $this;
    }
    public function orNotIn($field, array $keys)
    {
        $this->in($field, $keys, 'NOT ', 'OR');
        return $this;
    }
    public function between($field, $value1, $value2, $type = '', $andOr = 'AND')
    {
        $where = '(' . $field . ' ' . $type . 'BETWEEN ' . ($this->escape($value1) . ' AND ' . $this->escape($value2)) . ')';
        if ($this->grouped) {
            $where = '(' . $where;
            $this->grouped = false;
        }
        if (is_null($this->where)) {
            $this->where = $where;
        } else {
            $this->where = $this->where . ' ' . $andOr . ' ' . $where;
        }
        return $this;
    }
    public function notBetween($field, $value1, $value2)
    {
        $this->between($field, $value1, $value2, 'NOT ', 'AND');
        return $this;
    }
    public function orBetween($field, $value1, $value2)
    {
        $this->between($field, $value1, $value2, '', 'OR');
        return $this;
    }
    public function orNotBetween($field, $value1, $value2)
    {
        $this->between($field, $value1, $value2, 'NOT ', 'OR');
        return $this;
    }
    public function like($field, $data, $type = '', $andOr = 'AND')
    {
        $like = $this->escape($data);
        $where = $field . ' ' . $type . 'LIKE ' . $like;
        if ($this->grouped) {
            $where = '(' . $where;
            $this->grouped = false;
        }
        if (is_null($this->where)) {
            $this->where = $where;
        } else {
            $this->where = $this->where . ' ' . $andOr . ' ' . $where;
        }
        return $this;
    }
    public function orLike($field, $data)
    {
        $this->like($field, $data, '', 'OR');
        return $this;
    }
    public function notLike($field, $data)
    {
        $this->like($field, $data, 'NOT ', 'AND');
        return $this;
    }
    public function orNotLike($field, $data)
    {
        $this->like($field, $data, 'NOT ', 'OR');
        return $this;
    }
    public function limit($limit, $limitEnd = null)
    {
        if (! is_null($limitEnd)) {
            $this->limit = $limit . ', ' . $limitEnd;
        } else {
            $this->limit = $limit;
        }
        return $this;
    }
    public function offset($offset)
    {
        $this->offset = $offset;
        return $this;
    }
    public function pagination($perPage, $page)
    {
        $this->limit = $perPage;
        $this->offset = (($page > 0 ? $page : 1) - 1) * $perPage;
        return $this;
    }
    public function orderBy($orderBy, $orderDir = null)
    {
        if (! is_null($orderDir)) {
            $this->orderBy = $orderBy . ' ' . strtoupper($orderDir);
        } else {
            if (stristr($orderBy, ' ') || $orderBy == 'rand()') {
                $this->orderBy = $orderBy;
            } else {
                $this->orderBy = $orderBy . ' ASC';
            }
        }
        return $this;
    }
    public function groupBy($groupBy)
    {
        if (is_array($groupBy)) {
            $this->groupBy = implode(', ', $groupBy);
        } else {
            $this->groupBy = $groupBy;
        }
        return $this;
    }
    public function having($field, $op = null, $val = null)
    {
        if (is_array($op)) {
            $fields = explode('?', $field);
            $where = '';
            foreach ($fields as $key => $value) {
                if (! empty($value)) {
                    $where .= $value . (isset($op[$key]) ? $this->escape($op[$key]) : '');
                }
            }
            $this->having = $where;
        } elseif (! in_array($op, $this->op)) {
            $this->having = $field . ' > ' . $this->escape($op);
        } else {
            $this->having = $field . ' ' . $op . ' ' . $this->escape($val);
        }
        return $this;
    }
    public function numRows()
    {
        return $this->numRows;
    }
    public function insertId()
    {
        return $this->insertId;
    }
    public function error()
    {
        $msg = '<h1>Database Error</h1>';
        $msg .= '<h4>Query: <em style="font-weight:normal;">"' . $this->query . '"</em></h4>';
        $msg .= '<h4>Error: <em style="font-weight:normal;">' . $this->error . '</em></h4>';
        if ($this->debug === true) {
            die($msg);
        }
        throw new \PDOException($this->error . '. (' . $this->query . ')');
    }
    public function get($type = false)
    {
        $this->limit = 1;
        $query = $this->getAll(true);
        if ($type === true) {
            return $query;
        }
        return $this->query($query, false, (($type == 'array') ? true : false));
    }
    public function getAll($type = false)
    {
        $query = 'SELECT ' . $this->select . ' FROM ' . $this->from;
        if (! is_null($this->join)) {
            $query .= $this->join;
        }
        if (! is_null($this->where)) {
            $query .= ' WHERE ' . $this->where;
        }
        if (! is_null($this->groupBy)) {
            $query .= ' GROUP BY ' . $this->groupBy;
        }
        if (! is_null($this->having)) {
            $query .= ' HAVING ' . $this->having;
        }
        if (! is_null($this->orderBy)) {
            $query .= ' ORDER BY ' . $this->orderBy;
        }
        if (! is_null($this->limit)) {
            $query .= ' LIMIT ' . $this->limit;
        }
        if (! is_null($this->offset)) {
            $query .= ' OFFSET ' . $this->offset;
        }
        if ($type === true) {
            return $query;
        }
        return $this->query($query, true, (($type === 'array') ? true : false));
    }
    public function insert(array $data, $type = false)
    {
        $query = 'INSERT INTO ' . $this->from;
        $values = array_values($data);
        if (isset($values[0]) && is_array($values[0])) {
            $column = implode(', ', array_keys($values[0]));
            $query .= ' (' . $column . ') VALUES ';
            foreach ($values as $value) {
                $val = implode(', ', array_map([$this, 'escape'], $value));
                $query .= '(' . $val . '), ';
            }
            $query = trim($query, ', ');
        } else {
            $column = implode(', ', array_keys($data));
            $val = implode(', ', array_map([$this, 'escape'], $data));
            $query .= ' (' . $column . ') VALUES (' . $val . ')';
        }
        if ($type === true) {
            return $query;
        }
        $query = $this->query($query, false);
        if ($query) {
            $this->insertId = $this->getConnection()->lastInsertId();
            return $this->insertId();
        }
        return false;
    }
    public function update(array $data, $type = false)
    {
        $query = 'UPDATE ' . $this->from . ' SET ';
        $values = [];
        foreach ($data as $column => $val) {
            $values[] = $column . '=' . $this->escape($val);
        }
        $query .= implode(',', $values);
        if (! is_null($this->where)) {
            $query .= ' WHERE ' . $this->where;
        }
        if (! is_null($this->orderBy)) {
            $query .= ' ORDER BY ' . $this->orderBy;
        }
        if (! is_null($this->limit)) {
            $query .= ' LIMIT ' . $this->limit;
        }
        if ($type === true) {
            return $query;
        }
        return $this->query($query, false);
    }
    public function delete($type = false)
    {
        $query = 'DELETE FROM ' . $this->from;
        if (! is_null($this->where)) {
            $query .= ' WHERE ' . $this->where;
        }
        if (! is_null($this->orderBy)) {
            $query .= ' ORDER BY ' . $this->orderBy;
        }
        if (! is_null($this->limit)) {
            $query .= ' LIMIT ' . $this->limit;
        }
        if ($query == 'DELETE FROM ' . $this->from) {
            $query = 'TRUNCATE TABLE ' . $this->from;
        }
        if ($type === true) {
            return $query;
        }
        return $this->query($query, false);
    }
    public function analyze()
    {
        return $this->query('ANALYZE TABLE ' . $this->from, false);
    }
    public function check()
    {
        return $this->query('CHECK TABLE ' . $this->from, false);
    }
    public function checksum()
    {
        return $this->query('CHECKSUM TABLE ' . $this->from, false);
    }
    public function optimize()
    {
        return $this->query('OPTIMIZE TABLE ' . $this->from, false);
    }
    public function repair()
    {
        return $this->query('REPAIR TABLE ' . $this->from, false);
    }
    public function transaction()
    {
        if (! $this->transactionCount++) {
            return $this->getConnection()->beginTransaction();
        }
        $this->getConnection()->exec('SAVEPOINT trans' . $this->transactionCount);
        return $this->transactionCount >= 0;
    }
    public function commit()
    {
        if (! --$this->transactionCount) {
            return $this->getConnection()->commit();
        }
        return $this->transactionCount >= 0;
    }
    public function rollBack()
    {
        if (--$this->transactionCount) {
            $this->getConnection()->exec('ROLLBACK TO trans' . $this->transactionCount + 1);
            return true;
        }
        return $this->getConnection()->rollBack();
    }
    public function exec()
    {
        if (is_null($this->query)) {
            return null;
        }
        $query = $this->getConnection()->exec($this->query);
        $this->logsql();
        if (! $query) {
            $this->error = $this->getConnection()->errorInfo()[2];
            return $this->error();
        }
        return $query;
    }
    public function fetch($array = false, $all = false,$fetchModel=null)
    {
        if (is_null($this->query)) {
            return null;
        }
        $type =  \PDO::FETCH_ASSOC;
        $query = $this->getConnection()->query($this->query);
        $this->logsql();
        if (! $query) {
            $this->error = $this->getConnection()->errorInfo()[2];
            return $this->error();
        }
        if ($all) {
            return $this->formatResultSet($query->fetchAll($type),$array,$all,$fetchModel);
        }
        return $this->formatResultSet($query->fetch($type),$array,$all,$fetchModel,false);
    }
    protected function formatResultSet($result,$array=false,$all=false,$fetchModel,$new=false){

        if($array)
        {
            return $result;
        }
        if(!$result)
        {
            return $result;
        }
        if($all == false)
        {
            $result = [$result];
        }
        $new = $new?$new:($all==true || $new);
        $datas = [];
        foreach ($result as $key => $r) {
            
            $class = null;
            if(!$fetchModel)
            {
                $fetchModel = $this->fetchModel;
            }
            if(is_object($fetchModel))
            {
                $classname = $fetchModel->getClassNameFull();
                if(!$new)
                {
                    $class = $fetchModel;
                }
            }
            else
            {
                $classname = $fetchModel;
            }

            if(!$class)
            {
                
                $class = new $classname();
            }
            $class->setRawData($r);
            $datas[] = $class;
        }

        if(!$all)
        {
            $datas = $datas[0];
        }
        return $datas;

    }
    public function fetchAll($array = false)
    {
        return $this->fetch($array, true);
    }
    public function query($query, $all = true, $array = false,$fetchModel=null)
    {
        $this->reset();
        if (is_array($all) || func_num_args() === 1) {
            $params = explode('?', $query);
            $newQuery = '';
            foreach ($params as $key => $value) {
                if (! empty($value)) {
                    $newQuery .= $value . (isset($all[$key]) ? $this->escape($all[$key]) : '');
                }
            }
            $this->query = $newQuery;
            return $this;
        }
        $this->query = preg_replace('/\s\s+|\t\t+/', ' ', trim($query));
        $str = false;
        foreach (['select', 'optimize', 'check', 'repair', 'checksum', 'analyze'] as $value) {
            if (stripos($this->query, $value) === 0) {
                $str = true;
                break;
            }
        }
        $cache = false;
        if (! is_null($this->cache)) {
            $cache = $this->cache->getCache($this->query, $array);
        }
        if (! $cache && $str) {
            $sql = $this->getConnection()->query($this->query);
            $this->logsql();
            if ($sql) {
                $this->numRows = $sql->rowCount();
                if (($this->numRows > 0)) {
                    if ($all) {
                        $q = [];
                        while ($result = $sql->fetchAll(\PDO::FETCH_ASSOC)) {
                            $q[] = $result;
                        }
                        $this->result = $this->formatResultSet($q[0],$array,$all,$fetchModel);
                    } else {
                        $q = $sql->fetch(\PDO::FETCH_ASSOC);
                        $this->result = $this->formatResultSet($q,$array,$all,$fetchModel,false);

                    }
                }
                if (! is_null($this->cache)) {
                    $this->cache->setCache($this->query, $this->result);
                }
                $this->cache = null;
            } else {
                $this->cache = null;
                $this->error = $this->getConnection()->errorInfo()[2];
                return $this->error();
            }
        } elseif ((! $cache && ! $str) || ($cache && ! $str)) {
            $this->cache = null;
            $this->result = $this->getConnection()->exec($this->query);
            if ($this->result === false) {
                $this->error = $this->getConnection()->errorInfo()[2];
                return $this->error();
            }
        } else {
            $this->cache = null;
            $this->result = $cache;
            $this->numRows = count($this->result);
        }
        $this->queryCount++;
        return $this->result;
    }
    public function escape($data)
    {
        if ($data === null) {
            return 'NULL';
        }
        return $this->getConnection()->quote(trim($data));
    }
    public function cache($time)
    {
        $this->cache = new Cache($this->cacheDir, $time);
        return $this;
    }
    public function queryCount()
    {
        return $this->queryCount;
    }
    public function getQuery()
    {
        return $this->query;
    }

    protected function reset()
    {
        $this->select = '*';
        $this->from = null;
        $this->where = null;
        $this->limit = null;
        $this->offset = null;
        $this->orderBy = null;
        $this->groupBy = null;
        $this->having = null;
        $this->join = null;
        $this->grouped = false;
        $this->numRows = 0;
        $this->insertId = null;
        $this->query = null;
        $this->error = null;
        $this->result = [];
        $this->transactionCount = 0;
        return;
    }

    protected function logsql(){

        Cherry::$app->getLogger()->log("SQL",$this->query);
        
        
    }
}